using System;
using System.Collections.Generic;
using System.Data;
using Oracle.DataAccess.Client;
using RentalOfCars.Domain.Entities;
using RentalOfCars.Infrastructure.Entities;
using RentalOfCars.Server.Domain.Database;

namespace RentalOfCars.Server.Infrastructure.Database
{
    public class OracleAccessor : IDbAccessor
    {
        private static OracleAccessor _instance;
        private readonly OracleConnection _connection;

        private OracleAccessor(string connectionString)
        {
            try
            {
                _connection = new OracleConnection(connectionString);
            }
            catch (Exception exception)
            {
                if (_connection != null)
                {
                    if (_connection.State == ConnectionState.Open)
                    {
                        _connection.Close();
                    }
                    _connection.Dispose();
                }

                throw new DbException("Cannot create oracle connection.", exception);
            }
        }

        #region IDbAccessor Members

        public void Connect()
        {
            try
            {
                if (_connection.State == ConnectionState.Open || _connection.State == ConnectionState.Connecting)
                {
                    return;
                }

                _connection.Open();
            }
            catch (Exception exception)
            {
                throw new DbException("Cannot connect to the database server", exception);
            }
        }

        public void Disconnect()
        {
            try
            {
                _connection.Close();
            }
            catch (Exception exception)
            {
                throw new DbException("Cannot disconnect from the database server", exception);
            }
        }

        public IEmployee GetEmployee(string login)
        {
            string query =
                "SELECT E.ID, E.Person, E.Login, E.Password, E.Class, E.HireDate, E.FireDate, P.ID, P.Name, P.LastName, P.Patronymic, P.Passport, P.Phone, EC.ID, EC.Description FROM Employees E, Persons P, EmployeeClass EC WHERE EC.ID = E.Class AND E.Person = P.ID AND E.Login = '{0}'";
            query = string.Format(query, login);
            DataTable table = GetQueryTable(query);
            if (table.Rows.Count == 0)
            {
                return null;
            }

            int personId = Convert.ToInt32(table.Rows[0][7]);
            string name = Convert.ToString(table.Rows[0][8]);
            string lastName = Convert.ToString(table.Rows[0][9]);
            string patronymic = Convert.ToString(table.Rows[0][10]);
            string passport = Convert.ToString(table.Rows[0][11]);
            string phone = Convert.ToString(table.Rows[0][12]);
            int employeeId = Convert.ToInt32(table.Rows[0][0]);
            string password = Convert.ToString(table.Rows[0][3]);
            int classId = Convert.ToInt32(table.Rows[0][4]);
            DateTime hireDate = DateTime.MinValue;
            DateTime fireDate = DateTime.MinValue;
            try
            {
                hireDate = Convert.ToDateTime(table.Rows[0][5]);
            }
            catch (Exception)
            {
            }
            try
            {
                fireDate = Convert.ToDateTime(table.Rows[0][6]);
            }
            catch (Exception)
            {
            }
            string employeeClassDescription = Convert.ToString(table.Rows[0][14]);

            return new Employee(personId, name, lastName, login, patronymic, passport, phone, employeeId, password,
                                new EmployeeClass(classId, employeeClassDescription), hireDate, fireDate);
        }

        public IList<IClient> GetClients()
        {
            try
            {
                var clients = new List<IClient>();

                string query =
                    "SELECT C.ID, C.Person, C.Discount, P.ID, P.Name, P.LastName, P.Patronymic, P.Passport, P.Phone FROM Clients C, Persons P WHERE C.Person = P.ID";
                DataTable table = GetQueryTable(query);

                for (int i = 0; i < table.Rows.Count; i++)
                {
                    int clientId = Convert.ToInt32(table.Rows[i][0]);
                    int personId = Convert.ToInt32(table.Rows[i][1]);
                    int discountId = Convert.ToInt32(table.Rows[i][2]);
                    string name = Convert.ToString(table.Rows[i][3]);
                    string lastName = Convert.ToString(table.Rows[i][4]);
                    string patronymic = Convert.ToString(table.Rows[i][5]);
                    string passport = Convert.ToString(table.Rows[i][6]);
                    string phone = Convert.ToString(table.Rows[i][7]);

                    IDiscount discount = GetDiscount(discountId);
                    var client = new Client(personId, name, lastName, patronymic, passport, phone, clientId, discount);
                    clients.Add(client);
                }

                return clients;
            }
            catch (Exception exception)
            {
                throw new DbException("Cannot get list of all clients", exception);
            }
        }

        public IList<IDiscount> GetDiscounts()
        {
            throw new NotImplementedException();
        }

        public IClient AddClient(IClient client)
        {
            throw new NotImplementedException();
        }

        public void DeleteClient(int clientId)
        {
            throw new NotImplementedException();
        }

        public IList<IClient> GetRegularClients()
        {
            throw new NotImplementedException();
        }

        public IList<IClient> GetBestClients()
        {
            throw new NotImplementedException();
        }

        public IList<IClient> GetFineClients()
        {
            throw new NotImplementedException();
        }

        public IList<IEmployeeClass> GetEmployeeClasses()
        {
            string query = "SELECT ID, Description FROM EmployeeClass";
            DataTable table = GetQueryTable(query);

            if (table.Rows.Count == 0)
            {
                return new List<IEmployeeClass>();
            }

            var classes = new List<IEmployeeClass>();

            for (int i = 0; i < table.Rows.Count; i++)
            {
                int id = Convert.ToInt32(table.Rows[i][0]);
                string description = Convert.ToString(table.Rows[i][1]);

                var eClass = new EmployeeClass(id, description);
                classes.Add(eClass);
            }

            return classes;
        }

        public IEmployee AddEmployee(IEmployee employee)
        {
            string query =
                "INSERT INTO Persons (Name, LastName, Patronymic, Passport, Phone) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}') RETURNING ID INTO :pId";
            var command = new OracleCommand(query, _connection);
            var personIdParam = new OracleParameter("pId", OracleDbType.Int32) {Direction = ParameterDirection.Output};
            command.Parameters.Add(personIdParam);
            command.ExecuteNonQuery();

            query =
                "INSERT INTO Employees (Person, Login, Password, Class, HireDate) VALUES ({0}, '{1}', '{2}', {3}, (SELECT SYSTIMESTAMP FROM DUAL)) RETURNING ID INTO :pId";
            query = string.Format(query, personIdParam.Value, employee.Login, employee.Password, employee.Class.Id);
            ExecuteQuery(query);

            command = new OracleCommand(query, _connection);
            var employeeIdParam = new OracleParameter("pId", OracleDbType.Int32) {Direction = ParameterDirection.Output};
            command.Parameters.Add(employeeIdParam);
            command.ExecuteNonQuery();

            return new Employee((int) personIdParam.Value, employee.Name, employee.LastName, employee.Login,
                                employee.Patronymic, employee.Passport, employee.Phone,
                                (int) employeeIdParam.Value, employee.Password, employee.Class,
                                employee.HireDate, employee.FireDate);
        }

        public IList<IEmployee> GetEmployees()
        {
            var employees = new List<IEmployee>();
            string query =
                "SELECT E.ID, E.Person, E.Login, E.Password, E.Class, E.HireDate, E.FireDate, P.ID, P.Name, P.LastName, P.Patronymic, P.Passport, P.Phone, EC.ID, EC.Description FROM Employees E, Persons P, EmployeeClass EC WHERE EC.ID = E.Class AND E.Person = P.ID";
            DataTable table = GetQueryTable(query);
            if (table.Rows.Count == 0)
            {
                return new List<IEmployee>();
            }

            for (int i = 0; i < table.Rows.Count; i++)
            {
                int personId = Convert.ToInt32(table.Rows[i][7]);
                string name = Convert.ToString(table.Rows[i][8]);
                string lastName = Convert.ToString(table.Rows[i][9]);
                string patronymic = Convert.ToString(table.Rows[i][10]);
                string passport = Convert.ToString(table.Rows[i][11]);
                string phone = Convert.ToString(table.Rows[i][12]);
                int employeeId = Convert.ToInt32(table.Rows[i][0]);
                string password = Convert.ToString(table.Rows[i][3]);
                int classId = Convert.ToInt32(table.Rows[i][4]);
                string login = Convert.ToString(table.Rows[i][4]);
                DateTime hireDate = DateTime.MinValue;
                DateTime fireDate = DateTime.MinValue;
                try
                {
                    hireDate = Convert.ToDateTime(table.Rows[i][5]);
                }
                catch (Exception)
                {
                }
                try
                {
                    fireDate = Convert.ToDateTime(table.Rows[i][6]);
                }
                catch (Exception)
                {
                }
                string employeeClassDescription = Convert.ToString(table.Rows[0][14]);

                var emoloyee = new Employee(personId, name, lastName, login, patronymic, passport, phone, employeeId,
                                            password,
                                            new EmployeeClass(classId, employeeClassDescription), hireDate, fireDate);

                employees.Add(emoloyee);
            }

            return employees;
        }

        public void FireEmployee(IEmployee employee)
        {
            string query = "UPDATE Employees SET FireDate = (SELECT SYSTIMESTAMP FROM DUAL) WHERE ID = {0}";
            query = string.Format(query, employee.EmployeeId);
            ExecuteQuery(query);
        }

        #endregion

        public IDiscount GetDiscount(int discountId)
        {
            string query = "SELECT D.Value FROM Discount D WHERE D.ID = {0}";
            query = string.Format(query, discountId);
            DataTable table = GetQueryTable(query);

            if (table.Rows.Count == 0)
            {
                return null;
            }

            return new Discount(discountId, Convert.ToInt32(table.Rows[0][0]));
        }

        public static OracleAccessor GetInstance(string connectionString)
        {
            return _instance ?? (_instance = new OracleAccessor(connectionString));
        }

        private DataTable GetQueryTable(string query)
        {
            var adapter = new OracleDataAdapter(query, _connection);
            var dataSet = new DataSet();
            adapter.Fill(dataSet);
            return dataSet.Tables[0];
        }

        public IList<ICar> GetCars()
        {
            throw new NotImplementedException();
        }
        public ICar AddCar(ICar car)
        {
            throw new NotImplementedException();
        }
        public void DeleteCar(int carId)
        {
            throw new NotImplementedException();
        }
        public IList<IBrand> GetBrands()
        {
            throw new NotImplementedException();
        }
        public IList<IFeature> GetFeatures()
        {
            throw new NotImplementedException();
        }

        public void ExecuteQuery(string query)
        {
            var cmd = new OracleCommand(query, _connection);
            cmd.ExecuteNonQuery();
        }
    }
}